﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;

namespace RH.EBS.Core.Repository.DBUtility
{
    public class OledbDBAccess
    {
        public static string DBConnectionString =
        ConfigurationManager.AppSettings.Get("DBConnection");

        public bool CancelFlag = false;

        private OleDbConnection CreateDBConnection()
        {
            OleDbConnection myConn = new OleDbConnection(OledbDBAccess.DBConnectionString);
            return myConn;
        }


        /// <summary>
        /// 执行SqlItem查询，返回DataSet。多种类型数据库
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteSqlItemType(SqlItem item)
        {
            DataSet ds = new DataSet();
            OleDbConnection myConn = this.CreateDBConnection();

            OleDbCommand myCommand = null;
            OleDbDataAdapter myAdapter = null;
            try
            {
                myCommand = myConn.CreateCommand();
                myAdapter = new OleDbDataAdapter();
                myAdapter.SelectCommand = myCommand;

                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new OleDbParameter(param.Name, param.Value));
                }

                myAdapter.Fill(ds);
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                //add by hao 
                throw ex;
            }
            finally
            {
                myConn.Close();
            }
            return ds;
        }


        #region 基本sql操作，以字符串Sql形式操作数据库。
        /// <summary>
        /// 打开单独数据表获取数据集，并返回DataSet。
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public DataSet OpenTable(string TableName)
        {
            OleDbConnection myConn = this.CreateDBConnection();
            DataSet myDataSet = new DataSet();
            OleDbDataAdapter myDataAdapter;

            try
            {
                myConn.Open();
                myDataAdapter = new OleDbDataAdapter("select * from " + TableName, myConn);
                myDataAdapter.Fill(myDataSet);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return myDataSet;
        }

        /// <summary>
        /// 可执行复杂的sql语句获取多表或多字段的数据集，并返回DataSet。
        /// </summary>
        /// <param name="StrSql"></param>
        /// <returns></returns>
        public DataSet ExecuteSql(string StrSql)
        {
            OleDbConnection myConn = this.CreateDBConnection();
            DataSet myDataSet = new DataSet();
            OleDbDataAdapter myDataAdapter;

            try
            {
                myConn.Open();
                myDataAdapter = new OleDbDataAdapter(StrSql, myConn);
                myDataAdapter.Fill(myDataSet);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return myDataSet;
        }

        /// <summary>
        /// 执行单句Sql。数据的添加、修改、删除、保存等操作。
        /// </summary>
        /// <param name="StrSql"></param>
        /// <returns></returns>
        public bool OperateDataACD(string StrSql)
        {
            bool bolResult = false;

            OleDbConnection myConn = this.CreateDBConnection();
            OleDbTransaction myTrans = null;
            OleDbCommand myCommand = null;

            try
            {
                myConn.Open();

                myTrans = myConn.BeginTransaction();
                myCommand = myConn.CreateCommand();

                myCommand.Transaction = myTrans;

                myCommand.CommandText = StrSql;
                myCommand.ExecuteNonQuery();
                myTrans.Commit();

                bolResult = true;
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }

                bolResult = false;

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return bolResult;
        }

        /// <summary>
        /// 同时执行多条Sql。数据的添加、修改、删除、保存等操作。
        /// </summary>
        /// <param name="TranString"></param>
        /// <returns></returns>
        public bool ExecuteTran(ArrayList TranString)
        {
            if (TranString == null || TranString.Count == 0)
            {
                return false;
            }

            bool bolResult = false;
            OleDbConnection myConn = this.CreateDBConnection();

            OleDbCommand myCommand = null;
            OleDbTransaction myTrans = null;

            try
            {
                myConn.Open();
                myTrans = myConn.BeginTransaction();
                myCommand = myConn.CreateCommand();
                myCommand.Transaction = myTrans;

                foreach (string strSql in TranString)
                {
                    myCommand.CommandText = strSql;
                    myCommand.ExecuteNonQuery();
                }

                myTrans.Commit();
                bolResult = true;
            }
            catch (Exception ex)
            {
                bolResult = false;

                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }

                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return bolResult;
        }

        #endregion


        #region 基本数据库操作，以SqlItem对象的形式操作数据库。
        /// <summary>
        /// 使用SqlItem对象数组，同时执行多条Sql。数据的添加、修改、删除、保存等操作。
        /// </summary>
        /// <param name="arySqlItem"></param>
        /// <returns></returns>
        public string ExecuteSqlItemArray(SqlItemArray arySqlItem)
        {
            List<SqlItem> list = arySqlItem.SqlItemList;

            if (list == null || list.Count == 0)
            {
                return "没有数据更改！";
            }

            string strResult = "";
            OleDbConnection myConn = this.CreateDBConnection();

            OleDbCommand myCommand = null;
            OleDbTransaction myTrans = null;

            try
            {
                myConn.Open();
                myTrans = myConn.BeginTransaction();
                myCommand = myConn.CreateCommand();
                myCommand.Transaction = myTrans;

                foreach (SqlItem item in list)
                {
                    if (this.CancelFlag == true)
                    {
                        throw new Exception("程序被强行取消！");
                    }

                    myCommand.CommandText = item.StrSql;
                    myCommand.Parameters.Clear();
                    if (item.StrSql == "p_session_parameters")
                    {
                        myCommand.CommandType = CommandType.StoredProcedure;
                        foreach (SqlParameters param in item.ParameterList)
                        {
                            OleDbParameter SqlParameter = new OleDbParameter(param.Name, param.Value);
                            SqlParameter.Direction = ParameterDirection.Input;
                            myCommand.Parameters.Add(SqlParameter);
                        }
                    }
                    else
                    {
                        myCommand.CommandType = CommandType.Text;
                        foreach (SqlParameters param in item.ParameterList)
                        {
                            if (param.Value.ToString() == "True")
                                param.Value = "Y";
                            if (param.Value.ToString() == "False")
                                param.Value = "N";
                            if (param.Value.ToString() == "")
                                param.Value = "";
                            myCommand.Parameters.Add(new OleDbParameter(param.Name, param.Value));
                        }
                    }
                    myCommand.ExecuteNonQuery();
                }
                myTrans.Commit();
                strResult = "OK";
            }
            catch (Exception ex)
            {
                strResult = "更新失败！";

                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return strResult;
        }

        /// <summary>
        /// 执行SqlItem查询，返回DataSet。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteSqlItem(SqlItem item)
        {
            DataSet ds = new DataSet();

            OleDbConnection myConn = this.CreateDBConnection();

            OleDbCommand myCommand = null;
            OleDbDataAdapter myAdapter = null;

            try
            {
                myCommand = myConn.CreateCommand();
                myAdapter = new OleDbDataAdapter();
                myAdapter.SelectCommand = myCommand;

                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new OleDbParameter(param.Name, param.Value));
                }

                myAdapter.Fill(ds);
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return ds;
        }

        /// <summary>
        /// 执行SqlItem查询，返回DataSet。connstr 数据库连接字符串
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteSqlItem(SqlItem item, string connStr)
        {
            DataSet ds = new DataSet();

            OleDbConnection myConn = new OleDbConnection(connStr);

            OleDbCommand myCommand = null;
            OleDbDataAdapter myAdapter = null;

            try
            {
                myCommand = myConn.CreateCommand();
                myAdapter = new OleDbDataAdapter();
                myAdapter.SelectCommand = myCommand;

                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new OleDbParameter(param.Name, param.Value));
                }

                myAdapter.Fill(ds);
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return ds;
        }


        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteScalarSqlItem(SqlItem item)
        {
            object result = null;

            OleDbConnection myConn = this.CreateDBConnection();

            OleDbCommand myCommand = null;

            try
            {
                myCommand = myConn.CreateCommand();

                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new OleDbParameter(param.Name, param.Value));
                }

                myConn.Open();
                result = myCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            if (Convert.IsDBNull(result) || result == null)
            {
                result = "";
            }

            return result.ToString();
        }

        /// <summary>
        /// 执行SqlItem，并返回所影响行数。add by hao
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public int ExecuteNonQuerySqlItem(SqlItem item)
        {
            int result = -1;

            OleDbConnection myConn = this.CreateDBConnection();
            OleDbCommand myCommand = null;

            try
            {
                myCommand = myConn.CreateCommand();

                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    myCommand.Parameters.Add(new OleDbParameter(param.Name, param.Value));
                }

                myConn.Open();
                result = myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }

                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return result;
        }

        /// <summary>
        /// 执行写日志的存储过程，返回操作结果。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteLogProcedureSqlItem(SqlItem item)
        {
            OleDbConnection myConn = this.CreateDBConnection();

            OleDbCommand myCommand = null;

            try
            {
                myCommand = myConn.CreateCommand();

                //storedProcedure
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = item.StrSql;
                foreach (SqlParameters param in item.ParameterList)
                {
                    OleDbParameter oraParam = new OleDbParameter(param.Name, param.Value);
                    switch (param.Name)
                    {
                        case "pSqlText":
                            oraParam.Direction = ParameterDirection.Input;
                            oraParam.Size = 50000;
                            break;
                        case "pSqlCode":
                            oraParam.Direction = ParameterDirection.Output;
                            oraParam.Size = 2000;
                            break;
                        case "pSqlErrm":
                            oraParam.Direction = ParameterDirection.Output;
                            oraParam.Size = 2000;
                            break;
                    }
                    myCommand.Parameters.Add(oraParam);
                }
                myConn.Open();
                myCommand.ExecuteNonQuery();

                return myCommand.Parameters[2].Value.ToString();
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                throw ex;
            }
            finally
            {
                myConn.Close();
            }
        }

        /// <summary>
        /// 执行存储过程，返回操作成功标志。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool ExecuteStoredProcedureSqlItem(SqlItem item)
        {
            bool result = false;

            OleDbConnection myConn = this.CreateDBConnection();

            OleDbCommand myCommand = null;

            try
            {
                myCommand = myConn.CreateCommand();

                //storedProcedure
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = item.StrSql;
                if (item.StrSql == "p_exesql")
                {
                    foreach (SqlParameters param in item.ParameterList)
                    {
                        OleDbParameter oraParam = new OleDbParameter(param.Name, param.Value);
                        switch (param.Name)
                        {
                            case "pSqlText":
                                oraParam.Direction = ParameterDirection.Input;
                                break;
                            case "pSqlCode":
                                oraParam.Direction = ParameterDirection.Output;
                                break;
                            case "pSqlErrm":
                                oraParam.Direction = ParameterDirection.Output;
                                break;
                        }
                        myCommand.Parameters.Add(oraParam);
                    }
                }
                else
                {
                    foreach (SqlParameters param in item.ParameterList)
                    {
                        OleDbParameter oraParam = new OleDbParameter(param.Name, param.Value);
                        oraParam.Direction = ParameterDirection.Input;
                        myCommand.Parameters.Add(oraParam);
                    }
                }

                myConn.Open();
                myCommand.ExecuteNonQuery();

                result = true;
            }
            catch (Exception ex)
            {
                LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, ex.ToString());

                if (myCommand != null)
                {
                    LogWriter.Write(LOG_CATEGORY.SYSTEM, LOG_LEVEL.ERROR, myCommand.CommandText);
                }
                throw ex;
            }
            finally
            {
                myConn.Close();
            }

            return result;
        }



        #endregion


        #region 以SqlItem对象的二进制流为参数进行数据库操作。
        /// <summary>
        /// 使用SqlItem对象数组，同时执行多条Sql。数据的添加、修改、删除、保存等操作。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteSqlItemArrayBinary(byte[] item)
        {
            return ExecuteSqlItemArray(SqlBuilder.DeserializeSqlItemArray(item));
        }

        /// <summary>
        /// 执行SqlItem查询，返回DataSet。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteSqlItemBinary(byte[] item)
        {
            return ExecuteSqlItem(SqlBuilder.DeserializeSqlItem(item));
        }

        /// <summary>
        /// 执行SqlItem查询，返回DataSet。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public DataSet ExecuteSqlItemTypeBinary(byte[] item)
        {
            return ExecuteSqlItemType(SqlBuilder.DeserializeSqlItem(item));
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteScalarSqlItemBinary(byte[] item)
        {
            return ExecuteScalarSqlItem(SqlBuilder.DeserializeSqlItem(item));
        }

        /// <summary>
        /// 执行SqlItem，并返回所影响行数。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public int ExecuteNonQuerySqlItemBinary(byte[] item)
        {
            return ExecuteNonQuerySqlItem(SqlBuilder.DeserializeSqlItem(item));
        }


        /// <summary>
        /// 执行存储过程，返回操作成功标志。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool ExecuteStoredProcedureSqlItemBinary(byte[] item)
        {
            return ExecuteStoredProcedureSqlItem(SqlBuilder.DeserializeSqlItem(item));
        }

        /// <summary>
        /// 执行存储过程，返回操作成功标志。
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public string ExecuteLogProcedureSqlItemBinary(byte[] item)
        {
            return ExecuteLogProcedureSqlItem(SqlBuilder.DeserializeSqlItem(item));
        }
        #endregion
    }
}
